var express = require('express');
const { connection } = require('../model/db') // 获取连接实例
const router = express.Router()


// 示例
// router.get("/msglist", (req, res) => {
//     connection.query(`select * from user;`, (err, data) => {
//         if (err) {
//             console.log("err", err)
//             res.send('query error')
//         } else {
//             // 将 MySQL 查询结果作为路由返回值
//             res.send({
//                 code: 200,
//                 msg: "获取数据成功",
//                 data
//             })
//         }
//     })
// })


// 考试管理相关接口


// 查询所有题目  出题
router.get("/getQueBank", (req, res) => {
    // 0学院场景  1企业场景
    const scene = req.query.scene
    const deptid = req.query.deid
    // 分页
    let limit = req.query.pageSize
    let page = req.query.pageNum
    let a = (page * 1 - 1) * limit * 1
    let sql = `select a.id,a.write_name,a.scene,b.dept_name,b.id as dept_id from question_bank a JOIN department b on a.dept_id = b.id LIMIT ${a},${limit * 1}`
    let sqlnum = `select a.id,a.write_name,a.scene,b.dept_name,b.id as dept_id from question_bank a JOIN department b on a.dept_id = b.id`
    if (scene && deptid) {
        sql = `select a.id,a.write_name,a.scene,b.dept_name,b.id as dept_id from question_bank a JOIN department b on a.dept_id = b.id WHERE scene = ${scene} && dept_id = ${deptid} LIMIT ${a},${limit * 1}`
        sqlnum = `select a.id,a.write_name,a.scene,b.dept_name,b.id as dept_id from question_bank a JOIN department b on a.dept_id = b.id WHERE scene = ${scene} && dept_id = ${deptid}`
    } else if (scene) {
        sql = `select a.id,a.write_name,a.scene,b.dept_name,b.id as dept_id from question_bank a JOIN department b on a.dept_id = b.id WHERE scene = ${scene} LIMIT ${a},${limit * 1}`
        sqlnum = `select a.id,a.write_name,a.scene,b.dept_name,b.id as dept_id from question_bank a JOIN department b on a.dept_id = b.id WHERE scene = ${scene}`
    } else if (deptid) {
        sql = `select a.id,a.write_name,a.scene,b.dept_name,b.id as dept_id from question_bank a JOIN department b on a.dept_id = b.id WHERE dept_id = ${deptid} LIMIT ${a},${limit * 1}`
        sqlnum = `select a.id,a.write_name,a.scene,b.dept_name,b.id as dept_id from question_bank a JOIN department b on a.dept_id = b.id WHERE dept_id = ${deptid}`
    }
    connection.query(sqlnum, (err, datab) => {
        if (err) {
            console.log("err", err)
            res.send('query error')
        } else {
            let total = datab.length
            connection.query(sql, (err, data) => {
                if (err) {
                    console.log("err", err)
                    res.send('query error')
                } else {
                    // 将 MySQL 查询结果作为路由返回值
                    res.send({
                        code: 200,
                        msg: "获取数据成功",
                        data,
                        total
                    })
                }
            })
        }
    })
})

// 出题接口 添加试卷
router.post("/addDeptPaper", (req, res) => {
    let { paper_name, dept_id, write_id, begin_time, end_time, teacher_id } = req.body
    if (paper_name && dept_id && write_id && begin_time && end_time && teacher_id) {
        // 判断该学院有没有试卷
        connection.query(`SELECT * FROM dept_paper WHERE dept_id = ${dept_id}`, (err, data) => {
            if (err) {
                console.log("err", err)
                res.send({
                    code: 400,
                    msg: "参数错误",
                });
            } else {
                if (data.length === 0) {
                    connection.query(`INSERT INTO dept_paper(paper_name,dept_id,write_id,begin_time,end_time,teacher_id) VALUES('${paper_name}',${dept_id},${write_id},'${begin_time}','${end_time}',${teacher_id})`, (err, data) => {
                        if (err) {
                            console.log("err", err)
                            res.send('query error')
                        } else {
                            // 将 MySQL 查询结果作为路由返回值
                            res.send({
                                code: 200,
                                msg: "选题成功",
                            })
                        }
                    })
                } else {
                    res.send({
                        code: 400,
                        msg: "该学院已有试卷",
                    })
                }
            }
        })

    } else {
        res.send(
            {
                code: 412,
                msg: "缺少参数"
            }
        )
    }

})
// 根据学院查询所有班级
router.get("/getClassRoom", (req, res) => {
    // 选题方id
    let idd = req.query.id
    // 分页
    let limit = req.query.pageSize || 10
    let page = req.query.pageNum || 1

    let a = (page * 1 - 1) * limit * 1
    if (idd && limit && page) {
        connection.query(`SELECT answer_id as id  from select_rule WHERE select_id = ${idd}`, (err, data) => {
            if (err) {
                console.log("err", err)
                res.send('query error')
            } else {
                let id = data[0].id
                connection.query(`select s.classroom,d.dept_name from student s join department d on s.college_id=d.id WHERE college_id = ${id} GROUP BY classroom`, (err, datab) => {
                    if (err) {
                        console.log("err", err)
                        res.send('query error')
                    } else {
                        let total = datab.length
                        connection.query(`select s.classroom,d.dept_name from student s join department d on s.college_id=d.id WHERE college_id = ${id} GROUP BY classroom LIMIT ${a},${limit * 1}`, (err, data) => {
                            if (err) {
                                console.log("err", err)
                                res.send('query error')
                            } else {
                                // 将 MySQL 查询结果作为路由返回值
                                res.send({
                                    code: 200,
                                    msg: "获取班级数据成功",
                                    data,
                                    total
                                })
                            }
                        })
                    }
                })
            }
        })

    } else {
        res.send(
            {
                code: 412,
                msg: "缺少参数"
            }
        )
    }

})
// 根据班级查询所有学生姓名以及身份证号
router.get("/getStuName", (req, res) => {
    let name = req.query.name
    let dept_id = req.query.deptId
    // 分页
    let limit = req.query.pageSize || 10
    let page = req.query.pageNum || 1
    let a = (page * 1 - 1) * limit * 1
    if (name && limit && page) {
        connection.query(`select stu_name,card_id,s.id from student s join select_rule sr on s.college_id=sr.answer_id WHERE classname = '${name}' and sr.select_id='${dept_id}'`, (err, datab) => {
            if (err) {
                console.log("err", err)
                res.send('query error')
            } else {
                let total = datab.length
                connection.query(
                  `select stu_name,card_id,s.id from student s join select_rule sr on s.college_id=sr.answer_id WHERE classname = '${name}' and sr.select_id='${dept_id}' LIMIT ${a},${
                    limit * 1
                  }`,
                  (err, data) => {
                    if (err) {
                      console.log("err", err);
                      res.send("query error");
                    } else {
                      // 将 MySQL 查询结果作为路由返回值
                      res.send({
                        code: 200,
                        msg: "获取所有学生成功",
                        data,
                        total,
                      });
                    }
                  }
                );
            }
        })
    } else {
        res.send(
            {
                code: 412,
                msg: "缺少参数"
            }
        )
    }

})
// 学生提交试卷
router.post("/addJuanStuda", (req, res) => {
    let { paper_id, stu_id, answer } = req.body
    if (paper_id && stu_id && answer) {
        connection.query(`select * from student_paper where paper_id='${paper_id}' and stu_id = '${stu_id}';`, (err, data) => {
            if (err) {
                console.log("err", err)
                res.send('query error')
            } else {
                // 将 MySQL 查询结果作为路由返回值
                if (data.length === 0) {
                    connection.query(`INSERT INTO student_paper(paper_id,stu_id,stu_answer,score) VALUES(${paper_id},${stu_id},"${answer}",0)`, (err, data) => {
                        if (err) {
                            console.log("err", err)
                            res.send('query error')
                        } else {
                            // 将 MySQL 查询结果作为路由返回值
                            res.send({
                                code: 200,
                                msg: "提交成功",
                            })
                        }
                    })
                } else {
                    res.send({
                        code: 400,
                        msg: "您已提交该试卷，不能重复提交噢~",
                    })
                }

            }
        })



    } else {
        res.send(
            {
                code: 412,
                msg: "缺少参数"
            }
        )
    }

})

// 根据学生id查试卷
router.get("/getJuanStu", (req, res) => {
    let id = req.query.id
    if (id) {
        connection.query(`select a.id,a.stu_answer,a.stu_id,b.write_name,c.dept_name,c.id as dept_id,a.score,state,d.paper_name,e.stu_name from student_paper a JOIN question_bank b JOIN department c join dept_paper d join student e on a.paper_id = d.id and d.write_id=b.id and b.dept_id = c.id and a.stu_id=e.id WHERE a.stu_id = ${id}`, (err, data) => {
            if (err) {
                console.log("err", err)
                res.send('query error')
            } else {
                // 将 MySQL 查询结果作为路由返回值
                res.send({
                    code: 200,
                    msg: "获取数据成功",
                    data
                })
            }
        })
    } else {
        res.send(
            {
                code: 412,
                msg: "缺少参数"
            }
        )
    }

})

// 评分接口
router.post("/setScore", (req, res) => {
    const { id, stu_id, score } = req.body
    if (id, stu_id, score) {
        connection.query(`UPDATE student_paper set score = ${score} where id = ${id} and stu_id = ${stu_id}`, (err, data) => {
            if (err) {
                console.log("err", err)
                res.send('query error')
            } else {
                // 将 MySQL 查询结果作为路由返回值
                res.send({
                    code: 200,
                    msg: "评分成功",
                })
            }
        })
    } else {
        res.send(
            {
                code: 412,
                msg: "缺少参数"
            }
        )
    }

})
// 修改状态已阅
router.post("/setStatejuan", (req, res) => {
    const { id } = req.body
    if (id) {
        connection.query(`UPDATE student_paper SET state = true WHERE id = ${id}`, (err, data) => {
            if (err) {
                console.log("err", err)
                res.send('query error')
            } else {
                // 将 MySQL 查询结果作为路由返回值
                res.send({
                    code: 200,
                    msg: "修改成功",
                })
            }
        })
    } else {
        res.send(
            {
                code: 412,
                msg: "缺少参数"
            }
        )
    }

})


// 根据班级查询所有学生的分数
router.get("/getStuScore", (req, res) => {
    let name = req.query.deptName
    if (name) {
        connection.query(`SELECT b.id,b.stu_name,b.card_id,a.score from student_paper a JOIN student as b ON a.stu_id = b.id  WHERE classroom = '${name}'`, (err, data) => {
            if (err) {
                console.log("err", err)
                res.send('query error')
            } else {
                // 将 MySQL 查询结果作为路由返回值
                res.send({
                    code: 200,
                    msg: "获取数据成功",
                    data
                })
            }
        })
    } else {
        res.send(
            {
                code: 412,
                msg: "缺少参数-班级号"
            }
        )
    }

})

module.exports = router;